/*
 * ServerDb.java
 *
 */
package jhelp;

import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.net.ServerSocket;
import java.net.Socket;
import java.net.SocketException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * This class presents server directly working with database.
 * The complete connection string should take the form of:<br>
 * <code><pre>
 *     jdbc:subprotocol://servername:port/datasource:user=username:password=password
 * </pre></code>
 * Sample for using MS Access data source:<br>
 * <code><pre>
 *  private static final String accessDBURLPrefix
 *      = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
 *  private static final String accessDBURLSuffix
 *      = ";DriverID=22;READONLY=false}";
 *  // Initialize the JdbcOdbc Bridge Driver
 *  try {
 *         Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
 *      } catch(ClassNotFoundException e) {
 *         System.err.println("JdbcOdbc Bridge Driver not found!");
 *      }
 *
 *  // Example: method for connection to a Access Database
 *  public Connection getAccessDBConnection(String filename)
 *                           throws SQLException {
 *       String databaseURL = accessDBURLPrefix + filename + accessDBURLSuffix;
 *       return DriverManager.getConnection(databaseURL, "", "");
 *   }
 *</pre></code>
 *  @author <strong >Y.D.Zakovryashin, 2009</strong>
 */
public class ServerDb implements JHelp {

    public final static String DEFAULT_DB_URL = "jdbc:derby://localhost:1527/";
    public final static String DEFAULT_DB_NAME = "j-help";
    public final static String DEFAULT_DB_USER = "app";

    private ServerSocket serverSocket;
    private Socket clientSocket;
    private ObjectInputStream input;
    private ObjectOutputStream output;

    private Connection dbConnection;
    private PreparedStatement insertTblTermStatement;
    private PreparedStatement insertTblDefStatement;
    private PreparedStatement selectDefinitionsStatement;
    private PreparedStatement selectDefinitionsStatementByTermId;
    private PreparedStatement selectTermStatement;
    private PreparedStatement selectTermStatementByValue;
    private PreparedStatement deleteTermStatement;
    private PreparedStatement deleteDefinitionStatement;
    private PreparedStatement deleteAllDefinitionStatement;
    private PreparedStatement updateDefinitionStatement;


    /**
     * Creates a new instance of <code>ServerDb</code> with default parameters.
     * Default parameters are:<br>
     * <ol>
     * <li><code>ServerDb</code> host is &laquo;localhost&raquo;;</li>
     * <li>{@link java.net.ServerSocket} is opened on
     * {@link JHelp#DEFAULT_DATABASE_PORT};</li>
     * </ol>
     */
    public ServerDb() {
        this(DEFAULT_DATABASE_PORT);
    }

    /**
     * Constructor creates new instance of <code>ServerDb</code>. 
     * @param port defines port for {@link java.net.ServerSocket} object.
     */
    public ServerDb(int port) {
        try {
            serverSocket = new ServerSocket(port);
        } catch (IOException e) {
            System.err.println("Невозможно открыть сокет на порту "+port);
            e.printStackTrace();
            serverSocket = null;
        }
    }

    /**
     * Constructor creates new instance of <code>ServerDb</code>. 
     * @param args array of {@link String} type contains connection
     * parameters.
     */
    public ServerDb(String[] args) {
        int port = DEFAULT_DATABASE_PORT;
        if (args.length > 1) {
            try {
                port = Integer.parseInt(args[1]);
            } catch (NumberFormatException e) {
                System.err.println("Указан неверный порт в коммандной строке, использую порт по умолчанию: " + port);
            }
        }
        try {
            serverSocket = new ServerSocket(port);
        } catch (IOException e) {
            System.err.println("Невозможно открыть сокет");
            e.printStackTrace();
            serverSocket = null;
        }
    }

    /**
     * Start method for <code>ServerDb</code> application.
     * @param args array of {@link String} type contains connection
     * parameters.
     */
    public static void main(String[] args) {
        ServerDb srvDb = new ServerDb(args);
        int resConnect = JHelp.ERROR;
        if (args.length > 2) printUsage();
        System.out.print("Подключение к серверу ");
        if (args.length == 0) {
            System.out.print(ServerDb.DEFAULT_DB_URL+ServerDb.DEFAULT_DB_NAME+";user="+ServerDb.DEFAULT_DB_USER+" ... ");
            resConnect = srvDb.connect();
        }
        else {
            System.out.print(args[0]+" ... ");
            resConnect = srvDb.connect(new String[] {args[0]});
        }
        if (resConnect == JHelp.READY) {
            System.out.println("подключено");
            srvDb.run();
            System.out.println("Отключение от сервера базы данных");
            srvDb.disconnect();
        } else System.out.println("ошибка");
    }

    private static void printUsage() {
        System.out.println("Usage: \n\tServerDB 'db-url' 'server-db-port'\n\tDefault db-url '"+ServerDb.DEFAULT_DB_URL+ServerDb.DEFAULT_DB_NAME+";user="+ServerDb.DEFAULT_DB_USER+"'");
    }

    /**
     * Method defines job cycle for client request processing.
     */
    private void run() {
        if (serverSocket != null) {
            while (!Thread.currentThread().isInterrupted()) {
                try {
                    clientSocket = serverSocket.accept();
                    output = new ObjectOutputStream(clientSocket.getOutputStream());
                    input = new ObjectInputStream(clientSocket.getInputStream());
                    System.out.println("Установлено соединение с " + clientSocket.getRemoteSocketAddress());
                    while (!Thread.currentThread().isInterrupted()) {
                        Object inObj;
                        try {
                            inObj = input.readObject();
                        } catch (ClassNotFoundException e) {
                            System.err.println("Неизвестная команда ");
                            e.printStackTrace();
                            continue;
                        } catch (SocketException e) {
                            // remote side connection closed
                            System.out.println("Соединение закрыто");
                            break;
                        } catch (IOException e) {
                            System.err.println("Сетевая ошибка");
                            e.printStackTrace();
                            break;
                        }
                        System.out.println(inObj);

                        Data command;
                        if (inObj != null && inObj instanceof Data) {
                            command = (Data)inObj;
                            if (command.getOperation() != JHelp.DISCONNECT) command = getData(command);
                        } else command = new Data(JHelp.ERROR, null, null);
                        if (command.getOperation() != JHelp.DISCONNECT) {
                            try {
                                System.out.println("Send data: " + command);
                                output.writeObject(command);
                            } catch (IOException e) {
                                System.err.println("Сетевая ошибка");
                                e.printStackTrace();
                                break;
                            }
                        } else {
                            try {
                                input.close();
                                output.close();
                                if (clientSocket != null && !clientSocket.isClosed()) clientSocket.close();

                            } catch (IOException e) {
                                System.err.println("Ошибка при завершении работы");
                                e.printStackTrace();
                            }
                            break;
                        }
                    }
                } catch (SocketException e) {
                    // remote side connection closed
//                    System.out.println("Соединение закрыто");
                } catch (IOException e) {
                    System.err.println("Сетевая ошибка");
                    e.printStackTrace();

                }
                System.out.println("Соединение закрыто с " + clientSocket.getRemoteSocketAddress());
            }
            try {
                input.close();
                output.close();
                if (clientSocket != null && !clientSocket.isClosed()) clientSocket.close();

            } catch (IOException e) {
                System.err.println("Ошибка при завершении работы");
                e.printStackTrace();
            }
       } else {
            System.err.println("Ошибка при инициализации приложения");
        }
    }

    private int update(Data data) {
        if (dbConnection == null || updateDefinitionStatement == null || data.getKey() == null
                || data.getKey().getItem() == null || data.getValues() == null || data.getValues().length != 1) return JHelp.ERROR;
        int res = 0;
        try {
            dbConnection.setAutoCommit(false);
            updateDefinitionStatement.setString(1, data.getValues()[0].getItem());
            updateDefinitionStatement.setInt(2, data.getValues()[0].getId());
            res = updateDefinitionStatement.executeUpdate();
        } catch (SQLException e) {
            System.err.println("Ошибка при обновлении записи в БД");
            e.printStackTrace();
            return JHelp.ERROR;
        }
        if (res == 1) {
            try {
                dbConnection.commit();
            } catch (SQLException e) {
                System.err.println("Ошибка при фиксации обновлений в БД");
                e.printStackTrace();
            }
        }
        try {
            dbConnection.setAutoCommit(true);
        } catch (SQLException e) {
            System.err.println("Ошибка БД");
            e.printStackTrace();
        }
        return (res != 1) ? JHelp.ERROR : JHelp.OK;
    }

    private int delete(Data data) {
        if (dbConnection == null || deleteTermStatement == null || deleteDefinitionStatement == null || data.getKey() == null
                || data.getKey().getItem() == null || data.getKey().getItem().equalsIgnoreCase("")) return JHelp.ERROR;
        try {
            dbConnection.setAutoCommit(false);
            if (data.getValues() == null || data.getValues().length == 0) { //delete whole definitions and term
                deleteAllDefinitionStatement.setInt(1, data.getKey().getId());
                deleteAllDefinitionStatement.executeUpdate();
                deleteTermStatement.setInt(1, data.getKey().getId());
                deleteTermStatement.executeUpdate();
            }
            else {      // delete only definition
                for (Item it : data.getValues()) {
                    deleteDefinitionStatement.setInt(1, it.getId());
                    deleteDefinitionStatement.executeUpdate();
                }
                selectDefinitionsStatementByTermId.setInt(1, data.getKey().getId());
                ResultSet def = selectDefinitionsStatementByTermId.executeQuery();
                if (!def.next()) { //if term without any definitions, delete term also
                    deleteTermStatement.setInt(1, data.getKey().getId());
                    deleteTermStatement.executeUpdate();
                }
            }
            try {
                dbConnection.commit();
            } catch (SQLException e) {
                System.err.println("Ошибка при фиксации транзакции SQL: "+e.getMessage());
                e.printStackTrace();
                try {
                    dbConnection.rollback();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }

            dbConnection.setAutoCommit(true);
        } catch (SQLException e) {
            System.err.println("Ошибка при удалении записи: "+e.getMessage());
            e.printStackTrace();
            return JHelp.ERROR;
        }

        return JHelp.OK;
    }

    private int insert(Data data) {
        if (dbConnection == null || insertTblTermStatement == null || insertTblDefStatement == null
                || selectTermStatement == null || selectDefinitionsStatementByTermId == null || data.getKey() == null
                || data.getKey().getItem() == null || data.getKey().getItem().equalsIgnoreCase("")
                || data.getValues() == null || data.getValues().length < 1) return JHelp.ERROR;
        try {
            selectTermStatementByValue.setString(1, data.getKey().getItem().toUpperCase());
            ResultSet terms = selectTermStatementByValue.executeQuery();
            int termId = -1;
            if (terms.next()) termId = terms.getInt("id");
            dbConnection.setAutoCommit(false);
            int countRow;
            if (termId < 0) {
                insertTblTermStatement.setString(1, data.getKey().getItem());
                countRow = insertTblTermStatement.executeUpdate();
            } else countRow = 1;

            if (countRow > 0) {
                ResultSet iDs;
                if (termId < 0) iDs = insertTblTermStatement.getGeneratedKeys();
                else iDs = null;
                if (termId >= 0 ||  (iDs != null && iDs.next())) {
                    if (termId < 0) termId = iDs.getInt(1);
                    data.getKey().setId(termId);
                    for (Item item : data.getValues()) {
                        String definition = item.getItem();
                        definition = definition.substring(0, (definition.length() > JHelp.DEFINITION_MAX_LEN) ? JHelp.DEFINITION_MAX_LEN : definition.length());
                        insertTblDefStatement.setString(1, definition);
                        insertTblDefStatement.setInt(2, termId);
                        int defCount = insertTblDefStatement.executeUpdate();
                        if (defCount > 0) {
                            ResultSet defIds = insertTblDefStatement.getGeneratedKeys();
                            if (defIds.next()) item.setId(defIds.getInt(1));
                            else {
                                System.err.println("Ошибка при вставке в таблицу tblDefinitions, не создан ID");
                                return JHelp.ERROR;
                            }
                        }
                        else {
                            System.err.println("Ошибка при вставке в таблицу tblDefinitions");
                            return JHelp.ERROR;
                        }
                    }
                }
                else {
                    System.err.println("Ошибка при вставке в таблицу tblTerms, не создан ID");
                    return JHelp.ERROR;
                }
            }
            else {
                System.err.println("Ошибка при вставке в таблицу tblTerms");
                return JHelp.ERROR;
            }
        } catch (SQLException e) {
            System.err.println("Ошибка при выполнении запроса SQL: "+e.getMessage());
            e.printStackTrace();
            return JHelp.ERROR;
        }

        try {
            dbConnection.commit();
        } catch (SQLException e) {
            System.err.println("Ошибка при фиксации транзакции SQL: "+e.getMessage());
            e.printStackTrace();
            try {
                dbConnection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            return JHelp.ERROR;
        }


        try {
            dbConnection.setAutoCommit(true);
        } catch (SQLException e) {
            System.err.println("Ошибка AutoCommit=ON: "+e.getMessage());
            e.printStackTrace();
        }
        return JHelp.OK;

    }

    private int select(Data data) {
        if (dbConnection == null || insertTblTermStatement == null || insertTblDefStatement == null || data.getKey() == null
                || data.getKey().getItem() == null || data.getKey().getItem().equalsIgnoreCase("")) return JHelp.ERROR;

        try {
            selectDefinitionsStatement.setString(1, data.getKey().getItem().toUpperCase());
            ResultSet answer = selectDefinitionsStatement.executeQuery();
            List<Item> defList = new ArrayList<>();
            data.getKey().setId(-1);
            while (answer.next()) {
                data.getKey().setId(answer.getInt("id"));
                defList.add(new Item(answer.getInt("def_id"), answer.getString("definition"), JHelp.ORIGIN));
            }
            data.setValues(defList.toArray(new Item[0]));
        } catch (SQLException e) {
            System.err.println("Ошибка при выполнении select SQL: "+e.getMessage());
            e.printStackTrace();
            return JHelp.ERROR;
        }


        return JHelp.OK;
    }

    /**
     *
     * @return error code. The method returns {@link jhelp.JHelp#OK} if streams are
     * opened successfully, otherwise the method returns {@link jhelp.JHelp#ERROR}.
     */
    public int connect() {
        final String[] url = {DEFAULT_DB_URL + DEFAULT_DB_NAME + ";user=" + DEFAULT_DB_USER};
        return connect(url);
    }

    /**
     * Method sets connection to database and create {@link java.net.ServerSocket}
     * object for waiting of client's connection requests.
     * @return error code. Method returns {@link JHelp#READY} in success
     * case. Otherwise method return {@link JHelp#ERROR} or error code.
     */
    public int connect(String[] args) {
        String url = DEFAULT_DB_URL + DEFAULT_DB_NAME + ";user=" + DEFAULT_DB_USER;
        if (args.length == 1) url = args[0];

        try {
            dbConnection = DriverManager.getConnection(url);
        } catch (SQLException e) {
            System.err.println("Ошибка при соединении с базой данных: " + e.getMessage());
//            e.printStackTrace();
            return JHelp.ERROR;
        }
        try {
            insertTblTermStatement = dbConnection.prepareStatement("INSERT INTO TblTerms (term) VALUES (?)", PreparedStatement.RETURN_GENERATED_KEYS);
            insertTblDefStatement = dbConnection.prepareStatement("INSERT INTO tblDefinitions (definition, term_id) VALUES (?, ?)", PreparedStatement.RETURN_GENERATED_KEYS);
            selectDefinitionsStatement = dbConnection.prepareStatement("SELECT tblTerms.id AS id, term, tblDefinitions.id AS def_id, definition FROM TblTerms "+
                                                                            "INNER JOIN tblDefinitions ON tblTerms.id = tblDefinitions.term_id WHERE UPPER(term) = ?");
            selectDefinitionsStatementByTermId = dbConnection.prepareStatement("SELECT tblTerms.id AS id, term, tblDefinitions.id AS def_id, definition FROM TblTerms "+
                    "INNER JOIN tblDefinitions ON tblTerms.id = tblDefinitions.term_id WHERE tblTerms.id = ?");
            selectTermStatement = dbConnection.prepareStatement("SELECT * FROM TblTerms WHERE id = ? ORDER BY id ASC");
            selectTermStatementByValue = dbConnection.prepareStatement("SELECT * FROM TblTerms WHERE UPPER(term) = ? ORDER BY id ASC");
            deleteTermStatement = dbConnection.prepareStatement("DELETE FROM TblTerms WHERE id = ?");
            deleteAllDefinitionStatement = dbConnection.prepareStatement("DELETE FROM tblDefinitions WHERE term_id = ?");
            deleteDefinitionStatement = dbConnection.prepareStatement("DELETE FROM tblDefinitions WHERE id = ?");
            updateDefinitionStatement = dbConnection.prepareStatement("UPDATE tblDefinitions SET definition = ? WHERE id = ?");
        } catch (SQLException e) {
            System.err.println("Ошибка создания запросов к базе данных: " + e.getMessage());
            e.printStackTrace();
            return JHelp.ERROR;
        }
        return JHelp.READY;
    }

    /**
     * Method returns result of client request to a database.
     * @param data object of {@link Data} type with request to database.
     * @return object of {@link Data} type with results of request to a
     * database.
     * @see jhelp.Data
     * @since 1.0
     */
    public Data getData(Data data) {

        int result = JHelp.ERROR;
        switch (data.getOperation()) {
            case JHelp.SELECT:
                result = select(data);
                break;
            case JHelp.INSERT:
                result = insert(data);
                break;
            case JHelp.DELETE:
                result = delete(data);
                break;
            case JHelp.UPDATE:
                result = update(data);
                break;
            case JHelp.DISCONNECT:
                result = JHelp.DISCONNECT;
                break;
            default:
                System.err.println("Неизвестная команда в Data: " + data.getOperation());
                break;
        }

        data.setOperation(result);

        return data;
    }

    /**
     * Method disconnects <code>ServerDb</code> object from a database and closes
     * {@link java.net.ServerSocket} object.
     * @return disconnect result. Method returns {@link #DISCONNECT} value, if
     * the process ends successfully. Othewise the method returns error code,
     * for example {@link #ERROR}.
     * @see JHelp#DISCONNECT
     * @since 1.0
     */
    public int disconnect() {
        try {
            if (dbConnection != null && !dbConnection.isClosed()) {
                dbConnection.close();
            }
        } catch (SQLException e) {
            System.err.println("Ошибка при соединении с базой данных: " + e.getMessage());
            e.printStackTrace();
        } finally {
            try {
                if (serverSocket != null && !serverSocket.isClosed()) serverSocket.close();
                return JHelp.DISCONNECT;
            } catch (IOException e) {
                System.err.println("Ошибка при закрытии сокета: " + e.getMessage());
                e.printStackTrace();
            }
        }

        return JHelp.ERROR;
    }
}
